package io.github.junxworks.qt.modules.stock.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import io.github.junxworks.qt.modules.bi.vo.NvPair;
import io.github.junxworks.qt.modules.stock.dto.IndustryIndicatorCondition;
import io.github.junxworks.qt.modules.stock.dto.IndustryIndicatorDetailCondition;
import io.github.junxworks.qt.modules.stock.vo.IndustryIndicatorDetailVo;
import io.github.junxworks.qt.modules.stock.vo.IndustryStatVo;

@Mapper
public interface IndustryMapper {

	
	@Select("""
			select
					c.industry_name industry,
					m.con_name stockName,
					v.total_mv/10000 total_mv,
					v.total_mv / mv.smv mv_weight,
					i.*
			from
					d_stock_index_member m , 
					d_stock_index_classify c ,
					d_stock_fina_indicator i
			inner join (
				select
						max(i.id) id,
						i.ts_code,
						i.end_date
				from
						d_stock_index_member m
						inner join d_stock_index_classify c on
							m.index_code = c.index_code
							and c.`level` = 'L3',
						d_stock_fina_indicator i
				where
						c.industry_name= #{industry}
					and m.in_date <= #{startDate}
					and (m.out_date >= #{endDate} or m.out_date is null)
					and	m.con_code = i.ts_code
					and	i.end_date >= #{startDate}
					and i.end_date <= #{endDate}
				group by
						ts_code,
						end_date) ii on
					i.id = ii.id,
					(
				select
						m.index_name,
						v.end_date,
						sum(v.total_mv)smv
				from
						d_stock_index_member m
						inner join d_stock_index_classify c on
							m.index_code = c.index_code
							and c.`level` = 'L3',
						d_stock_mv v
				where
					c.industry_name= #{industry}
					and m.in_date <= #{startDate}
					and (m.out_date >= #{endDate} or m.out_date is null)
					and v.end_date >= #{startDate}
					and v.end_date <= #{endDate}
					and	m.con_code = v.ts_code
				group by
						m.index_name,
						v.end_date) mv,
					d_stock_mv v
			where
				m.index_code = c.index_code
				and c.industry_name = #{industry}
				and c.`level` = 'L3'
				and m.con_code = i.ts_code
				and m.in_date <= #{startDate}
				and (m.out_date >= #{endDate} or m.out_date is null)
				and i.end_date >= #{startDate}
				and i.end_date <= #{endDate}
				and mv.index_name = m.index_name
				and mv.end_date = i.end_date
				and v.ts_code = i.ts_code
				and v.end_date = i.end_date
			order by
				i.ts_code asc,
				i.end_date desc
			""")
	List<IndustryIndicatorDetailVo> queryIndicatorsDetail(IndustryIndicatorDetailCondition condition);
	
	@Select("""
			select
				date_format(i.end_date,'%Y-%m') name,
			    round(sum(${statExp})/10000,2) value
			from
					d_stock_index_member m ,
					d_stock_index_classify c ,
					d_stock_moneyflow_monthly i
			where
				m.index_code = c.index_code
				and c.industry_name = #{industry}
				and c.`level` = 'L3'
				and m.in_date <= #{startDate}
				and (m.out_date >= #{endDate} or m.out_date is null)
				and m.con_code = i.ts_code
				and i.end_date >= #{startDate}
				and i.end_date <= #{endDate}
			  group by name
			  order by name asc
			""")
	List<NvPair> statIndustryMoneyFlow(IndustryIndicatorDetailCondition condition);

	@Select("""
			select
				i.trade_mon name,
			    round(sum(i.total_mv)/10000,2) value
			from
					d_stock_index_member m ,
					d_stock_index_classify c ,
					d_stock_mv i
			where
				m.index_code = c.index_code
				and c.industry_name = #{industry}
				and c.`level` = 'L3'
				and m.in_date <= #{startDate}
				and (m.out_date >= #{endDate}
					or m.out_date is null)
				and m.con_code = i.ts_code
				and i.end_date >= #{startDate}
				and i.end_date <= #{endDate}
			  group by name
			  order by name asc
			""")
	List<NvPair> statIndustryMarketValue(IndustryIndicatorDetailCondition condition);
	
	@Select("""
			select
				c.industry_name industry,
				date_format(i.end_date,'%Y-%m') ym,
			    round(sum(buy_lg_amount+buy_elg_amount-sell_lg_amount-sell_elg_amount)/10000,2) value
			from
					d_stock_index_member m ,
					d_stock_index_classify c ,
					d_stock_moneyflow_monthly i
			where
				m.index_code = c.index_code
				and c.`level` = 'L3'
				and m.in_date <= #{startDate}
				and (m.out_date >= #{endDate} or m.out_date is null)
				and m.con_code = i.ts_code
				and i.end_date >= #{startDate}
				and i.end_date <= #{endDate}
			  group by c.industry_name,ym
			  order by ym desc,value desc
			""")
	List<IndustryStatVo> rankMoneyFlow(IndustryIndicatorCondition condition);
	
	@Select("""
			select
				c.industry_name industry,
				i.trade_mon ym,
			    round(sum(i.total_mv)/10000,2) value
			from
					d_stock_index_member m ,
					d_stock_index_classify c ,
					d_stock_mv i
			where
				m.index_code = c.index_code
				and c.`level` = 'L3'
				and m.in_date <= #{startDate}
				and (m.out_date >= #{endDate} or m.out_date is null)
				and m.con_code = i.ts_code
				and i.end_date >= #{startDate}
				and i.end_date <= #{endDate}
			  group by c.industry_name,ym
			  order by ym desc,value desc
			""")
	List<IndustryStatVo> rankMarketValue(IndustryIndicatorCondition condition);
}
